package com.soda.dao.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.soda.bean.Course;
import com.soda.bean.Student;
import com.soda.bean.User;
import com.soda.dao.BaseDao;
import com.soda.dao.CourseDao;

public class CourseDaoImpl extends BaseDao implements CourseDao {

	public int selectTotalCount(String couName) {
		try {
			getConnection();
			String sql = "select count(1) from course where 1=1 ";
			if (couName != null && !"".equals(couName)) {

				sql += "and cou_name like ?";
			}
			pt = conn.prepareStatement(sql);
			if (couName != null && !"".equals(couName)) {

				pt.setString(1, "%" + couName + "%");
			}
			res = pt.executeQuery();
			if (res.next()) {
				return res.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
		return 0;
	}

	public List<Course> selectResultByPage(int page, int per) {
		try {
			getConnection();
			String sql = "SELECT * FROM course c INNER JOIN `user` u ON u.use_id=c.user_id order by cou_id desc LIMIT ?,?";
			pt = conn.prepareStatement(sql);
			pt.setInt(1, (page - 1) * per);
			pt.setInt(2, per);
			res = pt.executeQuery();
			List<Course> list = new ArrayList<Course>();
			while (res.next()) {
				list.add(new Course(res.getInt(1), res.getString(2), res 
						.getString(3), res.getInt(4), res.getInt(5), res
						.getInt(6), res.getString(7), res.getString(8), res
						.getString(9), res.getString(10), new User(res
						.getString(15)), null));
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
		return null;
	}

	public Course getCourseById(int id) {
		try {
			getConnection();
			String sql = "SELECT * FROM course c INNER JOIN `user` u ON u.use_id=c.user_id where cou_id=?";
			pt = conn.prepareStatement(sql);
			pt.setInt(1, id);
			res = pt.executeQuery();
			// List<Course> list =new ArrayList<Course>();
			if (res.next()) {
				Course c = new Course(res.getInt(1), res.getString(2),
						res.getString(3), res.getInt(4), res.getInt(5),
						res.getInt(6), res.getString(7), res.getString(8),
						res.getString(9), res.getString(10), new User(
								res.getString(15)), null);
				return c;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
		return null;
	}

	public int couUpdate(Course c) {
		getConnection();
		StringBuffer sql = new StringBuffer(
				"update course set cou_name=?,cou_address=?,cou_type=?,cou_state=?,user_id=?,cou_money=?");

		if ((c.getCouOpentime() != null&&c.getCouOpentime()!="") && (c.getCouEndtime() != null&&c.getCouEndtime()!="")) {
			sql.append(",cou_opentime=?,cou_endtime=?");
		}
		sql.append(" where cou_id=?");
		try {
			int x=1;
			pt = conn.prepareStatement(sql.toString());
			pt.setString(x, c.getCouName());
			x++;
			pt.setString(x, c.getCouAddress());
			x++;
			pt.setInt(x, c.getCouType());
			x++;
			pt.setInt(x, c.getCouState());
			x++;
			pt.setInt(x, c.getUser().getUseId());
			x++;
			pt.setInt(x, c.getCouMoney());
			x++;
			if ((c.getCouOpentime() != null&&c.getCouOpentime()!="") && (c.getCouEndtime() != null&&c.getCouEndtime()!="")) {
				pt.setString(x, c.getCouOpentime());
				x++;
				pt.setString(x, c.getCouEndtime());
				x++;
			}
			pt.setInt(x, c.getCouId());
			return pt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
		return 0;

	}

	public int couresStateUpdateById(int couid, int coustate) {
		try {
			conn = getConnection();
			String sql = "update course set cou_state=? where cou_id=?";
			pt = conn.prepareStatement(sql);
			pt.setInt(1, coustate);
			pt.setInt(2, couid);
			return pt.executeUpdate();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
		return 0;
	}

	public User getIdAndSelectTeacherName(String tname) {
		try {
			conn = getConnection();
			String sql = "select * from user where use_name=?";
			pt = conn.prepareStatement(sql);
			pt.setString(1, tname);
			res = pt.executeQuery();
			if (res.next()) {
				User user = new User();
				user.setUseId(res.getInt(1));
				user.setUseName(res.getString(4));
				return user;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
		return null;

	}

	public int addcourse(Course c) {
		try {
			getConnection();
			String sql = "insert into course values(null,?,?,?,?,?,?,?,?,?,?)";
			pt = conn.prepareStatement(sql);
			pt.setString(1, c.getCouName());
			pt.setString(2, c.getCouAddress());
			pt.setInt(3, c.getCouType());
			pt.setInt(4, c.getCouState());
			pt.setInt(5, c.getCouMoney());
			pt.setString(6, c.getCouOpentime());
			pt.setString(7, c.getCouEndtime());
			pt.setString(8, c.getCouVideo());
			pt.setString(9, c.getCouRemark());
			pt.setInt(10, c.getUser().getUseId());
			return pt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
		return 0;
	}

	public List<Course> selectResultByPage(String couName, int page, int per) {
		// TODO Auto-generated method stub
		List<Course> list;
		int num=1;

		try {
			getConnection();
			String sql = "SELECT * FROM course c INNER JOIN `user` u ON u.use_id=c.user_id  where 1=1";
			if (couName != null && !"".equals(couName)) {

				sql += " and cou_name like ?";
			}
			sql += " order by cou_id desc limit ?,?";
			pt = conn.prepareStatement(sql);
			if (couName != null && !"".equals(couName)) {

				pt.setString(num, "%"+couName+"%");
			 num++;
			}
			pt.setInt(num, (page - 1) * per);
			num++;
			pt.setInt(num, per);
			res = pt.executeQuery();
			list = new ArrayList<Course>();
			while (res.next()) {
				list.add(new Course(res.getInt(1), res.getString(2), res
						.getString(3), res.getInt(4), res.getInt(5), res
						.getInt(6), res.getString(7), res.getString(8), res
						.getString(9), res.getString(10), new User(res
						.getString(15)), null));
			}
			return list;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	public List<Student> selStuByCouType(int id) {
		try {
			getConnection();
			String sql="SELECT	* FROM student s INNER JOIN stu_course sc ON s.stu_id=sc.stu_id INNER JOIN course c ON c.cou_id=sc.cou_id WHERE c.cou_type=1 AND c.cou_id=?";
			pt=conn.prepareStatement(sql);
			pt.setInt(1, id);
			res = pt.executeQuery();
			List<Student> list = new ArrayList<Student>();
			while(res.next()){
				Student stu = new Student();
				stu.setStuName(res.getString(4));
				stu.setStuSex(res.getInt(5));
				stu.setStuAge(res.getInt(6));
				stu.setStuSchool(res.getString(7));
				stu.setStuIdcard(res.getString(8));
				stu.setStuMajor(res.getString(9));
				list.add(stu);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			close();
		}
				
		return null;
	}

	public int selectTotalStudents(int id) {
		try {
			getConnection();
			String sql="select count(1) FROM student s INNER JOIN stu_course sc ON s.stu_id=sc.stu_id INNER JOIN course c ON c.cou_id=sc.cou_id WHERE c.cou_type=1 AND c.cou_id=?";
			pt=conn.prepareStatement(sql);
			pt.setInt(1, id);
			res=pt.executeQuery();
			if(res.next()){
				return res.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			close();
		}
		return 0;
		}
		
	}

